数据库Sequence创建与使用 |
您所在的位置:网站首页 › oracle 存储过程使用 › 数据库Sequence创建与使用 |
最近几天使用Oracle的sequence序列号,发现对如何创建、修改、使用存在很多迷茫点,在上网寻找答案后,根据各路大神的总结,汇总下对自己的学习成果; 在Oracle中sequence就是序号,每次提取完都会自动增加,步幅固定,它与表没有直接关系!
创建sequence语句: CREATE SEQUENCE seq_name-- seq_name为计数器的名字,自定;INCREMENT BY 1 -- 每次加幅度:1,2,3,....;START WITH 1 -- 起始序号,以实际生产情况而定;NOMAXvalue -- 不设置最大值,或设定最大值: maxvalue 9999;NOCYCLE -- 一直累加,不循环; 或循环使用 cycle ;CACHE 10; --设置缓存序列个数,如果系统down掉了或者其它情况将会导致序列不连续,也可以设置为---------NOCACHE
修改sequence: Alter sequence 可以修改sequence(除起始值)步幅、最大/最小值、是否循环、缓存个数 这些参数; 例:Alter Sequence seq_name Increment By 2 Maxvalue 9999 Cycle Cache 5; 需要修改sequence的起始值,则需要删除原有sequence,re-create重新创建;
应用sequence: sequence创建完成后,就可以使用sequence的两个参数 currval、nextval; currval查询sequence的当前值:select seq_name.currval from dual; nextval查询sequence下一个值:select seq_name.nextval from dual; 例:对某一张表使用:insert into tb_name(id,name) values(seq_name.nextval,'下一个计数');
删除sequence : drop sequence seq_name;
注: 1、currval是取当前值,所以一个新的计数器sequence必须先使用nextval后才可以使用currval否则会报错; 2、nextval是取下一个值,但第一次使用时取的是初始值,之后正常取下一个,且如果一个语句(不同的子句)里面有多个nextval,它们的取值可能是不同的; 3、如果指定CACHE值,ORACLE就可以预先在内存里面放置一些sequence,cache里面的取完后,oracle自动再取一组到cache。 优点:存取的快些,尤其是并发访问时。 缺点:使用cache或许会跳号, 比如数据库突然不正常down掉(shutdown abort),cache中的sequence就会丢失. 所以可以在创建的时候用nocache防止这种情况。 在Oracle数据库中,序列的用途是生成表的主键值,可以在插入语句中引用,也可以通过查询检查当前值,或使序列增至下一个值。本文我们主要介绍了序列的用法,希望能够对您有所帮助。在Oracle数据库中,什么是序列呢?它的用途是什么?序列(SEQUENCE)其实是序列号生成器,可以为表中的行自动生成序列号,产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值,可以在插入语句中引用,也可以通过查询检查当前值,或使序列增至下一个值。 创建序列需要CREATE SEQUENCE系统权限。 序列的创建语法如下: CREATE SEQUENCE 序列名 [INCREMENT BY n] [START WITH n] [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [] [{CACHE n|NOCACHE}];INCREMENT BY 用于定义序列的步长,如果省略,则默认为1,如果出现负值,则代表序列的值是按照此步长递减的。 START WITH 定义序列的初始值(即产生的第一个值),默认为1。 MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项,代表没有最大值定义,这时对于递增序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。 MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是?10的26次方;对于递增序列,最小值是1。 CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。 CACHE(缓冲)定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。 删除序列的语法是: DROP SEQUENCE 序列名;其中: 删除序列的人应该是序列的创建者或拥有DROP ANY SEQUENCE系统权限的用户。序列一旦删除就不能被引用了。 序列的某些部分也可以在使用中进行修改,但不能修改SATRT WITH选项。对序列的修改只影响随后产生的序号,已经产生的序号不变。 修改序列的语法如下: ALTER SEQUENCE 序列名 [INCREMENT BY n] [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [{CYCLE|NOCYCLE}] [{CACHE n|NOCACHE}];*不能修改序列的初始值 创建和删除序列 例1:创建序列: CREATE SEQUENCE ABC INCREMENT BY 1 START WITH 10 MAXVALUE 9999999 NOCYCLE NOCACHE; 执行结果: 序列已创建。 步骤2:删除序列: DROP SEQUENCE ABC; 执行结果: 序列已丢弃。 说明:以上创建的序列名为ABC,是递增序列,增量为1,初始值为10。该序列不循环,不使用内存。没有定义最小值,默认最小值为1,最大值为9 999 999。 序列的使用 如果已经创建了序列,怎样才能引用序列呢?方法是使用CURRVAL和NEXTVAL来引用序列的值。 在编号的过程中,产生间隙的原因多种多样。如果一个存储过程从一个序列中挑选某个数字,定为本地变量,但是从来不用它,这个数字就丢失了。它将不能再返回到原序列中,结果就造成数值序列中存在一个间隙。关系型数据库模型中不必担心这一点。但是有时候人们在意这一点,这些人想知道是哪些数字丢失了。 调用NEXTVAL将生成序列中的下一个序列号,调用时要指出序列名,即用以下方式调用: 序列名.NEXTVALCURRVAL用于产生序列的当前值,无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用NEXTVAL产生过序列的下一个值,先引用CURRVAL没有意义。调用CURRVAL的方法同上,要指出序列名,即用以下方式调用: 序列名.CURRVAL产生序列的值。 步骤1:产生序列的第一个值: SELECT ABC.NEXTVAL FROM DUAL; 执行结果: NEXTVAL —————— 10 步骤2:产生序列的下一个值: SELECT ABC.NEXTVAL FROM DUAL; 执行结果: NEXTVAL ——————- 11 产生序列的当前值: SELECT ABC.CURRVAL FROM DUAL; 执行结果: CURRVAL ——————– 11 说明:第一次调用NEXTVAL产生序列的初始值,根据定义知道初始值为10。第二次调用产生11,因为序列的步长为1。调用CURRVAL,显示当前值11,不产生新值。Oracle的解析函数为检查间隙提供了一种要快捷得多的方法。它们使你在使用完整的、面向集合的SQL处理的同时,仍然能够看到下一个行(LEAD)或者前一行(LAG)的数值。
在某张表中,存在一个id列(整数),我们希望在添加记录的时候,该列从1开始,自动的增长,怎么处理? 解决方式:oracle是利用“序列”(sequence)来完成的。
序列(sequence)介绍 oracle中,是通过使用序列(sequence)来处理自动增长列。 (1)可以为表中的列自动产生值。 (2)由用户创建数据库对象,并可由多个用户共享。 (3)一般用于主键或唯一列。
创建序列基本语法: create sequence 序列名称 start with 开始数字 increment by 增长数字 minvalue 最小值 maxvalue 最大值 cycle nocache 详细说明: start with 开始数字à从几开始 increment by 增长à步长,每次增长几个数 minvalue 最小值 maxvalue 最大值à可以不设置,不设置应写为nomaxvalue,也就是无穷大 cycle 循环,也就是说当长增长到最大值后,再从最小值开始重新增长 nocache 不设缓存
案例说明: create sequence my_seq --创建序列名 start with 1 --从1开始 increment by 1 --每次增长1 maxvalue 999999999 --最大值 //nomaxvalue(不设置最大值) minvalue 1 --最小值 cycle --循环 //nocycle(一直累加,不循环) nocache --不使用缓存 解释:从1开始,每次增长1,最大值999999999,之后循环从1开始。
create sequence myseq start with 0 increment by 1 minvalue 0 nomaxvalue nocycle nocache; 解释:从0开始,每次增长1,最小值0,无穷大,不循环一直累加。
sequence的使用: create table test1(id number primary key,name varchar2(32)); insert into test1 values(myseq.nextval,'abc'); insert into test1 values(myseq.nextval,'ddd'); 特别说明: 1、myseq:表示序列的名字,nextval:关键字,表示从序列中取下一个值。 2、sequence序列是需要配合number类型的列来使用; 3、sequence序列是要在主键或unique列上使用的。
问题: 如果system用户使用scott的sequence的序列时,sequence是从1增长还是从已使用到的数字开始? 答案:是从已使用到的数字接着增长。
细节说明: 看例子: insert into emp values(my_seq.nextval,'tomcat','clerk',7566,sysdate,1200,null,20); select my_seq.currval from dual; 注意: 第一次使用nextval返回的是初始值; 随后的nextval会自动增加你定义的increment by值,然后返回增加后的值; currval总是返回当前sequence的值,但是在第一次nextval初始化之后才能使用currval,否则会出错。 使用一次nextval会增加一次sequence的值,所以如果你在同一个语句里面使用多个nextval,其值就是不一样的。 如果指定cache值,oracle就可以预先在内存里面放置一些sequence,这样存取的快些。cache里面的取完后,oracle自动再取一组到cache。使用cache或许会跳号,比如数据库突然不正常down掉(shotdown abort),cache中的sequence就会丢失,所以可以在create sequence的时候用nocache防止这种情况。
一旦定义了某个序列,你就可以用currval,nextval currval:返回sequence的当前值 nextval:增加sequence的值,然后返回sequence值。 比如: 序列名.crrval 序列名.nextval
什么时候使用sequence? 不包含子查询、snapshot/view的select的语句 insert语句的子查询中 insert语句的values中 update的set中 如:update 表名 列值=序列名.nextval where 条件;
在sqlserver和mysql中都可以在定义表的时候,直接给指定自增长。 sqlserver中设置自增长 create table 表名(id int primary key identity(1,1),name varchar(32)); mysql中设置自增长 create table 表名(id int primary key auto_incrment,name varchar(32)); |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |